package controllers;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

public class Accessdb {

    /**
     * cse305 server
     */
		public static final String DB_URL = "mysql1.cs.stonybrook.edu:3306/jihhwang";
		public static final String DB_ID = "jihhwang";
		public static final String DB_PW = "107988935";
    /**
     * localhost mysql5.5 server
     */
//    public static final String DB_URL = "localhost:3306";
//    public static final String DB_ID = "root";
//    public static final String DB_PW = "1234";
    private String dburl = "jdbc:mysql://" + DB_URL;
    private String dbuser = DB_ID;
    private String dbpass = DB_PW;

    public void setDbUrl(String url) {
        dburl = url;
    }

    public String getDbUrl() {
        return dburl;
    }

    public void setDbUser(String user) {
        dbuser = user;
    }

    public String getDbUser() {
        return dbuser;
    }

    public void setDbPass(String pass) {
        dbpass = pass;
    }

    public String getDbPass() {
        return dbpass;
    }

    /**
     * Function for opening connection to the database
     */
    public Connection getConnection() {

        Connection conn = null;
        try {
        	
//        	Class.forName( "com.mysql.jdbc.Driver").newInstance();
//            java.util.Properties sysprops = System.getProperties();
//            sysprops.put("user", mysUserID);
//            sysprops.put("password", mysPassword);

            // connect to the database
//            conn = java.sql.DriverManager.getConnection(mysURL,
//                    sysprops);
        	
            System.out.println("Connected successfully to database using JConnect");
            conn = DriverManager.getConnection(getDbUrl(), getDbUser(), getDbPass());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * Function to close open connection to database
     */
    public void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //HashMap<String, String> sqlQueryMap = new HashMap("Posts.findByCircleId", "SELECT p FROM Posts p WHERE p.Circle = :circleId");
    public boolean checkUserIdDuplicate(String id) {
        boolean duplicate = false;
        Connection conn = getConnection();
        if (conn != null) {
            ResultSet rs = null;
            PreparedStatement ps = null;
            try {
                String escapedQuery = new String(id);
                escapedQuery.replace("'", "''");
                String sqlQuery = "SELECT id FROM jihhwang.logins WHERE id='" + id + "'";
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                    duplicate = true;
                    break;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }
        return duplicate;
    }

//    Table persons
//=============
//SSN, Last_Name, First_Name, Sex, Address, City, State, Zip_Code, Telephone, Email_Address, Date_Of_Birth
//-------------
//SSN              int(11) PK
//Last_Name        varchar(50)
//First_Name       varchar(50)
//Sex              varchar(45)
//Address          varchar(100)
//City             varchar(50)
//State            varchar(50)
//Zip_Code         int(11)
//Telephone        mediumtext
//Email_Address    varchar(50)
//Date_Of_Birth    datetime
    public boolean insertPerson(Integer ssn, String lastName, String firstName, String sex, String address, String city, String state, String zipCode, String telephone, String emailAddress, Date dateOfBirth) {
        System.out.println("Accessdb.insertPerson");
        Connection conn = getConnection();
        Statement st = null;
        boolean result = true;
        if (conn != null) {
            try {
                st = conn.createStatement();

                String sqlQuery = "INSERT INTO `jihhwang`.`persons` (`SSN`, `Last_Name`, `First_Name`, `Sex`, `Address`, `City`, `State`, `Zip_Code`, `Telephone`, `Email_Address`, `Date_Of_Birth`)" + " "
                        + "VALUES ('" + ssn + "', '" + lastName + "', '" + firstName + "', '" + sex + "', '" + address + "', '" + city + "', '" + state + "', '" + zipCode + "', '" + telephone + "', '" + emailAddress + "', '" + dateOfBirth + "');";
                st.executeUpdate(sqlQuery);
            } catch (SQLException e) {
                e.printStackTrace();
                result = false;
            } finally {
                try {
                    st.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                    result = false;
                }
                closeConnection(conn);
            }
            return result;
        } else {
            System.out.println("Connection Failed!");
            return false;
        }
    }

    public boolean insertUser(Integer ssn, Integer rating) {
        System.out.println("Accessdb.insertUser");
        Connection conn = getConnection();
        Statement st = null;
        boolean result = true;
        if (conn != null) {
            try {
                st = conn.createStatement();

                Date createTime = new Date(java.lang.System.currentTimeMillis());
                Calendar cal = Calendar.getInstance();
                cal.setTime(createTime);
                String date = String.format("%04d-%02d-%02d", cal.get(Calendar.YEAR),
                        (cal.get(Calendar.MONTH) + 1), cal.get(Calendar.DAY_OF_MONTH));

                String sqlQuery = "INSERT INTO jihhwang.users "
                        + "(`SSN`, `Account_Creation_Date`, `rating`)" + " "
                        + " VALUES ('" + ssn + "', '" + date + "', '" + rating + "');";
                st.executeUpdate(sqlQuery);
            } catch (SQLException e) {
                e.printStackTrace();
                result = false;
            } finally {
                try {
                    st.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                    result = false;
                }
                closeConnection(conn);
            }
            return result;
        } else {
            System.out.println("Connection Failed!");
            return false;
        }
    }

    public boolean insertEmployee(Integer ssn, Integer hourlyRate,
            Integer managerId) {

        System.out.println("Accessdb.insertEmployee");

        Connection conn = getConnection();
        Statement st = null;
        boolean result = true;
        if (conn != null) {
            try {
                st = conn.createStatement();
                Date createTime = new Date(java.lang.System.currentTimeMillis());
                Calendar cal = Calendar.getInstance();
                cal.setTime(createTime);
                String date = String.format("%04d-%02d-%02d", cal.get(Calendar.YEAR),
                        (cal.get(Calendar.MONTH) + 1), cal.get(Calendar.DAY_OF_MONTH));

                String sqlQuery = "INSERT INTO `jihhwang`.`employees` " + " "
                        //+ "(`Employee_Id`,`SSN`, `Start_Date`, `Hourly_Rate`, `Manager`)" + " """
                        + "(`SSN`, `Start_Date`, `Hourly_Rate`, `Manager`)" + " "
                        + " VALUES ('" + ssn + "', '" + date + "', '" + hourlyRate + "', '" + managerId + "');";
                st.executeUpdate(sqlQuery);
            } catch (SQLException e) {
                e.printStackTrace();
                result = false;
            } finally {
                try {
                    st.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                    result = false;
                }
                closeConnection(conn);
            }
            return result;
        } else {
            System.out.println("Connection Failed!");
            return false;
        }
    }

    public boolean insertManager(Integer ssn, Integer hourlyRate) {

        System.out.println("Accessdb.insertManager");

        Connection conn = getConnection();
        Statement st = null;
        boolean result = true;
        if (conn != null) {
            try {
                st = conn.createStatement();
                Date createTime = new Date(java.lang.System.currentTimeMillis());
                Calendar cal = Calendar.getInstance();
                cal.setTime(createTime);
                String date = String.format("%04d-%02d-%02d", cal.get(Calendar.YEAR),
                        (cal.get(Calendar.MONTH) + 1), cal.get(Calendar.DAY_OF_MONTH));

                String sqlQuery = "INSERT INTO `jihhwang`.`managers` " + " "
                        //+ "(`Employee_Id`,`SSN`, `Start_Date`, `Hourly_Rate`, `Manager`)" + " """
                        + "(`SSN`, `Start_Date`, `Hourly_Rate`)" + " "
                        + " VALUES ('" + ssn + "', '" + date + "', '" + hourlyRate + "');";
                st.executeUpdate(sqlQuery);
            } catch (SQLException e) {
                e.printStackTrace();
                result = false;
            } finally {
                try {
                    st.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                    result = false;
                }
                closeConnection(conn);
            }
            return result;
        } else {
            System.out.println("Connection Failed!");
            return false;
        }
    }

    public boolean insertEmployeePerson(Integer ssn, String lastName, String firstName, String sex, String address, String city, String state, String zipCode, String telephone, String emailAddress, Date dateOfBirth,
            Date startDate, Integer hourlyRate, Integer managerId) {
        if (insertPerson(ssn, lastName, firstName, sex, address, city, state, zipCode, telephone, emailAddress, dateOfBirth)) {
            return insertEmployee(ssn, hourlyRate, managerId);
        } else {
            return false;
        }
    }

    public boolean insertUserPerson(Integer ssn, String lastName, String firstName, String sex, String address, String city, String state, String zipCode, String telephone, String emailAddress, Date dateOfBirth,
            Integer rating) {
        System.out.println("Accessdb.insertUserPerson");
        if (insertPerson(ssn, lastName, firstName, sex, address, city, state, zipCode, telephone, emailAddress, dateOfBirth)) {
            return insertUser(ssn, rating);
        } else {
            return false;
        }
    }

    public boolean insertManagerPerson(Integer ssn, String lastName, String firstName, String sex, String address, String city, String state, String zipCode, String telephone, String emailAddress, Date dateOfBirth,
            Integer hourlyRate) {
        System.out.println("Accessdb.insertUserPerson");
        if (insertPerson(ssn, lastName, firstName, sex, address, city, state, zipCode, telephone, emailAddress, dateOfBirth)) {
            return insertManager(ssn, hourlyRate);
        } else {
            return false;
        }
    }
    
    public ArrayList<String[]> getSalesReportForMonth(Integer year, Integer month, Integer managerId) {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "SELECT A.Advertisement_Id, A.Employee, SUM(S.Number_Of_Units) AS TotalSold, SUM(S.Number_Of_Units*A.Unit_Price) AS TotalRevenue" +
        				 " FROM Advertisements A, Employees E, Purchases S" +
        				 " WHERE MONTH(S.Date) = " + month  +
        				 " AND YEAR(S.Date) = " + year +
        				 " AND S.Advertisement = A.Advertisement_Id" +
        				 " AND A.Employee = E.Employee_Id" +
        				 " AND E.Manager= " + managerId +
        				 " GROUP BY A.Advertisement_Id";

        ArrayList<String[]> salesReport = new ArrayList<String[]>();

        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String[] list = {rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4)};
                    salesReport.add(list);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }
        return salesReport;
    }
  
    public ArrayList<String[]> getAdvertisedList() {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "SELECT A.Item_Name, A.Unit_Price, A.Available_Units FROM Advertisements A";

        ArrayList<String[]> advertisedList = new ArrayList<String[]>();
        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String[] list = {rs.getString(1), rs.getString(2), rs.getString(3)};
                    advertisedList.add(list);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }
        return advertisedList;
    }
    
    
    
    
    
    public ArrayList<String[]> getTransactionsByItem(String itemName) {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "SELECT S.*, A.Item_Name FROM Advertisements A, Purchases S WHERE A.Item_Name = "+ "'" + itemName + "'";

        ArrayList<String[]> transactionsByItem = new ArrayList<String[]>();

        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                	String[] list = {rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7)};
                	transactionsByItem.add(list);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }
        return transactionsByItem;
    }
    
    public ArrayList<String[]> getTransactionsByUserName(String lastName, String firstName) {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "SELECT S.*, A.Item_Name FROM Advertisements A, Purchases S, Users U, Persons P WHERE P.Last_Name = "
        				  +"'"+ lastName +"'"+ "AND P.First_Name = '" +  firstName + "' " +
        					" AND P.SSN = U.SSN AND U.User_Id = S.User AND S.Advertisement = A.Advertisement_Id";

        ArrayList<String[]> transactionsByuserName = new ArrayList<String[]>();

        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                	String[] list = {rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6)};
                	transactionsByuserName.add(list);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }
        return transactionsByuserName;
    }
    
    
    public ArrayList<String[]> getListingOfRevenueByItem(String itemName) {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "SELECT  A.Advertisement_Id, A.Item_Name, A.Employee, SUM(S.Number_Of_Units) AS TotalSold, SUM(S.Number_Of_Units*A.Unit_Price) AS TotalRevenue" +
       					" FROM Advertisements A, Employees E, Purchases S" +
       					" WHERE S.Advertisement = A.Advertisement_Id AND A.Employee=E.Employee_Id And A.Item_Name = " +"'" + itemName + "'" +
       					" GROUP BY A.Advertisement_Id";

        ArrayList<String[]> item = new ArrayList<String[]>();

        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                	String[] list = {rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)};
                	item.add(list);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }
        return item;
    }
    
    public ArrayList<String[]> getListingOfRevenueByType(String itemType) {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "SELECT  A.Advertisement_Id, A.Item_Name, A.Type, A.Employee, SUM(S.Number_Of_Units) AS TotalSold, SUM(S.Number_Of_Units*A.Unit_Price) AS TotalRevenue" +
        				 " FROM Advertisements A, Employees E, Purchases S" + 
        				 " WHERE S.Advertisement = A.Advertisement_Id AND A.Employee=E.Employee_Id And A.Type = " + "'" + itemType + "'" +
        				 " GROUP BY A.Advertisement_Id";

        ArrayList<String[]> type = new ArrayList<String[]>();

        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                	String[] list = {rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)};
                	type.add(list);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }
        return type;
    }
    
    public ArrayList<String[]> getListingOfRevenueByUser(Integer userID) {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "SELECT  A.Advertisement_Id, A.Item_Name, A.Type, A.Employee, SUM(S.Number_Of_Units) AS TotalSold, SUM(S.Number_Of_Units*A.Unit_Price) AS TotalRevenue" +
        				 " FROM Advertisements A, Employees E, Purchases S" + 
        				 " WHERE S.Advertisement = A.Advertisement_Id AND A.Employee=E.Employee_Id And S.User = " +  userID +
        				 " GROUP BY A.Advertisement_Id";

        ArrayList<String[]> type = new ArrayList<String[]>();

        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                	String[] list = {rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)};
                	type.add(list);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }
        return type;
    }
    
    public ArrayList<String[]> getMostTotalRevenueEachEmployee() {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "SELECT E.Employee_Id, SUM(S.Number_Of_Units) AS TotalSold, SUM(S.Number_Of_Units*A.Unit_Price) AS TotalRevenue" +
        					" FROM Advertisements A, Employees E, Purchases S" +
        					" WHERE S.Advertisement = A.Advertisement_Id AND A.Employee=E.Employee_Id" +
        					" GROUP BY E.Employee_Id" +
        					" ORDER BY TotalRevenue DESC";

        ArrayList<String[]> employee = new ArrayList<String[]>();

        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                	String[] list = {rs.getString(1), rs.getString(2), rs.getString(3)};
                	employee.add(list);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }
        return employee;
    }
    
    public ArrayList<String[]> getMostTotalRevenueEachCustomer() {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "		SELECT	User_Id, Last_Name, First_Name,  SUM(S.Number_Of_Units*A.Unit_Price) AS TotalRevenue" +
        				  " FROM 	Managers M, Employees E, Advertisements A, Users U, purchases S, Persons P" +
        				  " WHERE	M.Manager_Id=MANAGER_ID" +
        				  " AND	E.Manager = M.Manager_Id" +
        				  " AND	A.Employee = E.Employee_Id" +
        				  " AND	S.Advertisement= A.Advertisement_Id" +
        				  " AND 	S.User = U.User_Id" +
        				  " AND   U.SSN = P.SSN" +
        				  " GROUP BY User_Id" +
        				  " ORDER BY TotalRevenue DESC";

        ArrayList<String[]> customer = new ArrayList<String[]>();

        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                	String[] list = {rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4)};
                	customer.add(list);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }
        return customer;
    }
    
    public ArrayList<String[]> getListOfMostActiveItem() {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "SELECT A.Advertisement_Id, A.Item_Name, sum(S.Number_Of_Units) AS TotalSold" +
        				" FROM Advertisements A, Purchases S" +
        				" WHERE S.Advertisement = A.Advertisement_Id" +
        				" GROUP BY A.Advertisement_Id" +
        				" ORDER BY TotalSold DESC";

        ArrayList<String[]> Item = new ArrayList<String[]>();

        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                	String[] list = {rs.getString(1), rs.getString(2), rs.getString(3)};
                	Item.add(list);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }
        return Item;
    }
 
    public ArrayList<String[]> getListOfCustomerWhoPurchasedParticularItem(String itemName) {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "SELECT  DISTINCT  P.First_Name, P.Last_Name, U.SSN, A.Item_Name"+
        				 " FROM advertisements A, purchases S, user_has_account H, Users U, Persons P" +
        				 " WHERE A.Item_Name = '" +itemName + "' AND A.advertisement_Id = S.Advertisement" +
        				 " AND S.Account = H.Account_Number AND H.User_Id = U.user_Id AND U.SSN = P.SSN";

        ArrayList<String[]> customer = new ArrayList<String[]>();

        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                	String[] list = {rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4)};
                	customer.add(list);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }
        return customer;
    }
    
    public ArrayList<String[]> getListOfItemCompany(String company) {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "SELECT A.Advertisement_Id, A.Company, A.Item_Name, A.Unit_Price, A.Available_Units FROM advertisements A WHERE A.Company = '" + company +"'";

        ArrayList<String[]> item = new ArrayList<String[]>();

        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                	String[] list = {rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)};
                	item.add(list);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }
        return item;
    }
    
    public ArrayList<String[]> getListOfItemName() {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "select Item_Name FROM advertisements";

        ArrayList<String[]> item = new ArrayList<String[]>();

        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                	String[] list = {rs.getString(1)};
                	item.add(list);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }
        return item;
    }
    
    public ArrayList<String[]> getListOfItemType() {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "select Type FROM advertisements";

        ArrayList<String[]> item = new ArrayList<String[]>();

        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                	String[] list = {rs.getString(1)};
                	item.add(list);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }
        return item;
    }

    
    
    
    
 // @SuppressWarnings("unchecked")
 	// public ArrayList<List<String>> getPostsByCircleId(Integer circleId) {
 	// System.out.println("Accessdb.getPostsByCircleId.circleId: " + circleId);
 	// Connection conn = getConnection();
 	// ResultSet rs = null;
 	// PreparedStatement ps = null;
 	// String sqlQuery = " SELECT * " + " FROM `jihhwang`.`posts`"
 	// + " WHERE Circle=" + "" + circleId + ";";
 	//
 	// ArrayList<List<String>> postsByCircleId = new ArrayList<List<String>>();
 	// if (conn != null) {
 	// try {
 	// ps = conn.prepareStatement(sqlQuery);
 	// rs = ps.executeQuery();
 	// System.out.println(sqlQuery);
 	// int i = 1;
 	// // ArrayList<SQLXML> postsByCircleId = new ArrayList<SQLXML>();
 	// // SQLXML sqlxml = rs.getSQLXML(column);
 	// // while (rs.next()) {
 	// // postsByCircleId.add( rs.getSQLXML("Author"));
 	// // postsByCircleId.add( rs.getSQLXML("content"));
 	// // i++;
 	// // }
 	// // InputStream binaryStream =
 	// // postsByCircleId.get(0).getBinaryStream();
 	// // DocumentBuilder parser =
 	// // DocumentBuilderFactory.newInstance().newDocumentBuilder();
 	// List<String> list;
 	// while (rs.next()) {
 	// list = new ArrayList<String>();
 	// for (int j = 1; j < 6; j++) {
 	// list.add(new String(rs.getString(j)));
 	// }
 	// // postsByCircleId.addAll((ArrayList<Array>)(rs.getArray("Author")));
 	// // postsByCircleId.addAll((ArrayList<Array>)(rs.getArray("Circle")));
 	// // postsByCircleId.addAll((ArrayList<Array>)(rs.getArray("Content")));
 	// //
 	// postsByCircleId.addAll((ArrayList<Array>)(rs.getArray("Comment_Count")));
 	// // postsByCircleId.addAll((ArrayList<Array>)(rs.getArray("datetime")));
 	// postsByCircleId.add(list);
 	// }
 	// // postsByCircleId.addAll((ArrayList<Array>)
 	// // (rs.getArray("Post_Id")));
 	// // postsByCircleId.addAll((ArrayList<Array>)(rs.getArray("Author")));
 	// // postsByCircleId.addAll((ArrayList<Array>)(rs.getArray("Circle")));
 	// // postsByCircleId.addAll((ArrayList<Array>)(rs.getArray("Content")));
 	// //
 	// postsByCircleId.addAll((ArrayList<Array>)(rs.getArray("Comment_Count")));
 	// // postsByCircleId.addAll((ArrayList<Array>)(rs.getArray("datetime")));
 	//
 	// } catch (SQLException e) {
 	// e.printStackTrace();
 	// } finally {
 	// try {
 	// ps.close();
 	// } catch (SQLException ex) {
 	// ex.printStackTrace();
 	// }
 	// closeConnection(conn);
 	// }
 	// } // if stmt ends.
 	// return postsByCircleId;
 	// }

 	// Table circles
 	// =============
 	// Circle_Id, Circle_NAME, Owner_Of_Circle, Type
 	// -------------
 	// Circle_Id int(11) PK
 	// Circle_NAME varchar(100)
 	// Owner_Of_Circle int(11)
 	// Type varchar(50)
 	//
 	// Table circle_members
 	// ====================
 	// Circle_Id, Member_Id
 	// --------------------
 	// Circle_Id int(11) PK
 	// Member_Id int(11) PK
 	//
 	// Table users
 	// ===========
 	// SSN, User_Id, Account_Creation_Date, Rating
 	// -----------
 	// SSN int(11)
 	// User_Id int(11) PK
 	// Account_Creation_Date datetime
 	// Rating int(11)

 	public JSONArray getUsersByCircleId(Integer circleId) {
 		System.out.println("Accessdb.getUsersByCircleId.circleId: " + circleId);
 		String sqlQuery = " SELECT P, U "
 				+ "	FROM Users U, Persons P, Circles C, Circle_Members CM"
 				+ " WHERE C.Circle_Id = " + "" + circleId
 				+ " AND CM.Circle_Id = " + "" + circleId + " AND U.User_Id = "
 				+ "CM.Member_Id" + " AND U.SSN = " + "P.SSN" + ";";
 		return injectQuery(sqlQuery);
 	}

 	public JSONArray getCircleInfoPageByCircleId(Integer circleId) {
 		System.out.println("Accessdb.getCircleInfoPageByCircleId.circleId: "
 				+ circleId);
 		String sqlQuery = " SELECT * "
 				+ "	FROM Users U, Persons P, Circles C, Circle_Members CM"
 				+ " WHERE C.Circle_Id = " + "" + circleId
 				+ " AND CM.Circle_Id = " + "" + circleId + " AND U.User_Id = "
 				+ "CM.Member_Id" + " AND U.SSN = " + "P.SSN" + ";";
 		return injectQuery(sqlQuery);
 		//JSONArray posts = getPostsByCircleId(circleId);
 	}

 	public JSONArray getPostsByCircleId(Integer circleId) {
 		System.out.println("Accessdb.getPostsByCircleId.circleId: " + circleId);
 		String sqlQuery = " SELECT * " + " FROM `jihhwang`.`posts`"
 				+ " WHERE Circle=" + "" + circleId + " ORDER BY Date DESC"
 				+ ";";
 		return injectQuery(sqlQuery);
 	}

 	public JSONArray getPostsByUserId(Integer userId) {
 		System.out.println("Accessdb.getPostsByUserId.userId: " + userId);
 		String sqlQuery = " SELECT * " + " FROM `jihhwang`.`posts`"
 				+ " WHERE Author=" + "" + userId + ";";
 		return injectQuery(sqlQuery);
 	}

 	// Table comments
 	// ==============
 	// Comment_Id, Date, Content, Post, Author
 	// --------------
 	// Comment_Id int(11) PK
 	// Date datetime
 	// Content varchar(50)
 	// Post int(11)
 	// Author int(11)

 	public JSONArray getCommentsByUserId(Integer userId) {
 		System.out.println("Accessdb.getCommentsByUserId.userId: " + userId);
 		String sqlQuery = " SELECT * " + " FROM `jihhwang`.`comments`"
 				+ " WHERE Author=" + "" + userId + ";";
 		return injectQuery(sqlQuery);
 	}

 	public JSONArray getCommentsByPostId(Integer postId) {
 		System.out.println("Accessdb.getCommentsByPostId.postId: " + postId);
 		String sqlQuery = " SELECT * " + " FROM `jihhwang`.`comments`"
 				+ " WHERE `Post_Id`=" + "" + postId + ";";
 		return injectQuery(sqlQuery);
 	}

 	public JSONArray getCirclesByCircleId(Integer circleId) {
 		System.out
 				.println("Accessdb.getCircleByCircleId.circleId: " + circleId);
 		String sqlQuery = " SELECT * " + " FROM `jihhwang`.`circles`"
 				+ " WHERE Circle=" + "" + circleId + ";";
 		return injectQuery(sqlQuery);
 	}

 	public JSONArray injectQuery(String sqlQuery) {
 		Connection conn = getConnection();
 		ResultSet rs = null;
 		PreparedStatement ps = null;
 		JSONArray result = new JSONArray();
 		if (conn != null) {
 			try {
 				ps = conn.prepareStatement(sqlQuery);
 				rs = ps.executeQuery();
 				System.out.println("queryInjected: " + sqlQuery);
 				result = convertResultSetToJSON(rs);

 			} catch (SQLException e) {
 				e.printStackTrace();
 			} finally {
 				try {
 					ps.close();
 				} catch (SQLException ex) {
 					ex.printStackTrace();
 				}
 				closeConnection(conn);
 			}
 		} // if stmt ends.
 		return result;
 	}

 	public String whatTimeNow() {
 		Date createTime = new Date(java.lang.System.currentTimeMillis());
 		Calendar cal = Calendar.getInstance();
 		cal.setTime(createTime);

 		String date = String.format("%04d-%02d-%02d", cal.get(Calendar.YEAR),
 				(cal.get(Calendar.MONTH) + 1), cal.get(Calendar.DAY_OF_MONTH));
 		return date;
 	}

 	// public JSONArray getCirclesByUserId(Integer userId) {
 	// System.out.println("Accessdb.getCircleByUserId.userId: " + userId);
 	// Connection conn = getConnection();
 	// ResultSet rs = null;
 	// PreparedStatement ps = null;
 	// String sqlQuery = " SELECT * " + " FROM `jihhwang`.`circles`"
 	// + " WHERE Circle=" + "" + circleId + ";";
 	//
 	// //ArrayList<List<String>> postsByCircleId = new
 	// ArrayList<List<String>>();
 	// JSONArray circlesByCircleId = new JSONArray();
 	// if (conn != null) {
 	// try {
 	// ps = conn.prepareStatement(sqlQuery);
 	// rs = ps.executeQuery();
 	// System.out.println(sqlQuery);
 	// circlesByCircleId = convertResultSetToJSON(rs);
 	// } catch (SQLException e) {
 	// e.printStackTrace();
 	// } finally {
 	// try {
 	// ps.close();
 	// } catch (SQLException ex) {
 	// ex.printStackTrace();
 	// }
 	// closeConnection(conn);
 	// }
 	// } // if stmt ends.
 	// return circlesByCircleId;
 	// }

 	
 	
 	public JSONArray convertResultSetToJSON(java.sql.ResultSet rs) {
		JSONArray json = new JSONArray();

		try {

			java.sql.ResultSetMetaData rsmd = rs.getMetaData();

			while (rs.next()) {
				int numColumns = rsmd.getColumnCount();
				JSONObject obj = new JSONObject();

				for (int i = 1; i < numColumns + 1; i++) {

					String column_name = rsmd.getColumnName(i);

					if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) {
						obj.put(column_name, rs.getArray(column_name));
					} else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) {
						obj.put(column_name, rs.getInt(column_name));
					} else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) {
						obj.put(column_name, rs.getBoolean(column_name));
					} else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) {
						obj.put(column_name, rs.getBlob(column_name));
					} else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) {
						obj.put(column_name, rs.getDouble(column_name));
					} else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) {
						obj.put(column_name, rs.getFloat(column_name));
					} else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) {
						obj.put(column_name, rs.getInt(column_name));
					} else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) {
						obj.put(column_name, rs.getNString(column_name));
					} else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) {
						obj.put(column_name, rs.getString(column_name));
					} else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) {
						obj.put(column_name, rs.getInt(column_name));
					} else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) {
						obj.put(column_name, rs.getInt(column_name));
					} else if (rsmd.getColumnType(i) == java.sql.Types.DATE) {
						obj.put(column_name, rs.getDate(column_name));
					} else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) {
						obj.put(column_name, rs.getTimestamp(column_name));
					} else {
						obj.put(column_name, rs.getObject(column_name));
					}

				}// end foreach
				json.add(obj);

			}// end while

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			// } catch (JSONException e) {
			// // TODO Auto-generated catch block
			// e.printStackTrace();
		}

		return json;
	}




    
    /////////////-- legacy version --///////////////////////////////////////////////////////////////
    
    
    
    public boolean duplicate(String id) {
        boolean duplicate = false;
        Connection conn = getConnection();
        if (conn != null) {
            ResultSet rs = null;
            PreparedStatement ps = null;
            try {
                String escapedQuery = new String(id);
                escapedQuery.replace("'", "''");
                String sqlQuery = "SELECT loginid FROM ftwi.member WHERE loginid='" + id + "'";
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                    duplicate = true;
                    break;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }
        return duplicate;
    }

    public String[] authenticate(String login_id, String login_pw) {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "select idnum, loginid, loginpw, name, user_type from ftwi.member where loginid=\"" + login_id + "\"";

        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                    if (rs.getString(3).equals(login_pw)) {
                        String[] user = {rs.getString(1), rs.getString(2), rs.getString(4), rs.getString(5)};
                        return user;
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }
        return null;
    }

//    public boolean insertUser(String id, String password, String name,
//            String phone, String address, String type) {
//        Connection conn = getConnection();
//        Statement st = null;
//        boolean result = true;
//        if (conn != null) {
//            try {
//                st = conn.createStatement();
//                Date createTime = new Date(java.lang.System.currentTimeMillis());
//                Calendar cal = Calendar.getInstance();
//                cal.setTime(createTime);
//
//                String date = String.format("%04d-%02d-%02d", cal.get(Calendar.YEAR),
//                        (cal.get(Calendar.MONTH) + 1), cal.get(Calendar.DAY_OF_MONTH));
//
//                String sqlQuery = "INSERT INTO ftwi.member "
//                        + "(loginid, loginpw, name, phone, address, reg_date, user_type)"
//                        + " VALUES ('" + id + "', '" + password + "', '" + name + "', '" + phone
//                        + "', '" + address + "', '" + date + "', '" + type + "');";
//                st.executeUpdate(sqlQuery);
//            } catch (SQLException e) {
//                e.printStackTrace();
//                result = false;
//            } finally {
//                try {
//                    st.close();
//                } catch (SQLException ex) {
//                    ex.printStackTrace();
//                    result = false;
//                }
//                closeConnection(conn);
//            }
//            return result;
//        } else {
//            System.out.println("Connection Failed!");
//            return false;
//        }
//    }
    public boolean insertHotel(String managerid, String name, String address, String phone, String picture, String description) {
        Connection conn = getConnection();
        Statement st = null;
        boolean result = true;
        if (conn != null) {
            try {
                st = conn.createStatement();

                String sqlQuery = "INSERT INTO ftwi.hotel_info "
                        + "(managerid, hotel_name, hotel_addr, hotel_tel, hotel_detail, hotel_picture)"
                        + " VALUES ('" + managerid + "', '" + name + "', '" + address + "', '" + phone
                        + "', '" + picture + "', '" + description + "');";
                st.executeUpdate(sqlQuery);
            } catch (SQLException e) {
                e.printStackTrace();
                result = false;
            } finally {
                try {
                    st.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                    result = false;
                }
                closeConnection(conn);
            }
            return result;
        } else {
            System.out.println("Connection Failed!");
            return false;
        }
    }

    public boolean insertRoom(String hotelid, String name, String number, String picture, String description) {
        Connection conn = getConnection();
        Statement st = null;
        boolean result = true;
        if (conn != null) {
            try {
                st = conn.createStatement();

                String sqlQuery = "INSERT INTO ftwi.room "
                        + "(hotelid, room_name, room_num, detail_room, room_img)"
                        + " VALUES ('" + hotelid + "', '" + name + "', '" + number + "', '" + description
                        + "', '" + picture + "');";
                st.executeUpdate(sqlQuery);
            } catch (SQLException e) {
                e.printStackTrace();
                result = false;
            } finally {
                try {
                    st.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                    result = false;
                }
                closeConnection(conn);
            }
            return result;
        } else {
            System.out.println("Connection Failed!");
            return false;
        }
    }

    public boolean insertReservation(String userid, String roomid, String[] reserve_date) {
        Connection conn = getConnection();
        Statement st = null;
        boolean result = true;
        if (conn != null) {
            try {
                st = conn.createStatement();

                for (int i = 0; i < reserve_date.length; i++) {
                    String sqlQuery = "INSERT INTO ftwi.reserve "
                            + "(roomid, userid, reserve_date)"
                            + " VALUES ('" + roomid + "', '" + userid + "', '" + reserve_date[i] + "');";
                    st.executeUpdate(sqlQuery);
                }
            } catch (SQLException e) {
                e.printStackTrace();
                result = false;
            } finally {
                try {
                    st.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                    result = false;
                }
                closeConnection(conn);
            }
            return result;
        } else {
            System.out.println("Connection Failed!");
            return false;
        }
    }

    public ArrayList<String> getAllHotel() {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "select hotel_name from ftwi.hotel_info";

        ArrayList<String> hotel = new ArrayList<String>();

        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                    hotel.add(rs.getNString(1));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }
        return hotel;
    }

    public ArrayList<String[]> getHotelByName(String name) {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "select idhotel, hotel_name, hotel_addr, hotel_tel, hotel_detail from ftwi.hotel_info WHERE hotel_name LIKE '%" + name + "%'";

        ArrayList<String[]> hotels = new ArrayList<String[]>();
        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String[] hotel = {rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)};
                    hotels.add(hotel);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }
        return hotels;
    }

    public ArrayList<String[]> getHotelByLocation(String location) {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "select idhotel, hotel_name, hotel_addr, hotel_tel, hotel_detail from ftwi.hotel_info WHERE hotel_addr LIKE '%" + location + "%'";

        ArrayList<String[]> hotels = new ArrayList<String[]>();
        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String[] hotel = {rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)};
                    hotels.add(hotel);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }
        return hotels;
    }

    public ArrayList<String[]> getHotelByRoom(String number) {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = null;
        if (number.compareTo("All") == 0) {
            sqlQuery = "select distinct idhotel, hotel_name, hotel_addr, hotel_tel, hotel_detail "
                    + "from ftwi.hotel_info, ftwi.room "
                    + "WHERE idhotel=hotelid";
        } else {
            sqlQuery = "select distinct idhotel, hotel_name, hotel_addr, hotel_tel, hotel_detail "
                    + "from ftwi.hotel_info, ftwi.room "
                    + "WHERE idhotel=hotelid and room_num='" + number + "'";
        }

        ArrayList<String[]> hotels = new ArrayList<String[]>();
        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String[] hotel = {rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)};
                    hotels.add(hotel);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }

        return hotels;
    }

    public ArrayList<String> getHotelName() {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;

        String sqlQuery = "select distinct hotel_name "
                + "from ftwi.hotel_info";

        ArrayList<String> hotels = new ArrayList<String>();
        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String hotel = rs.getString(1);
                    hotels.add(hotel);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }

        return hotels;
    }

    public ArrayList<String> getHotelLocation() {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;

        String sqlQuery = "select distinct hotel_addr "
                + "from ftwi.hotel_info";

        ArrayList<String> hotels = new ArrayList<String>();
        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String hotel = rs.getString(1);
                    hotels.add(hotel);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }

        return hotels;
    }

    public ArrayList<String[]> getHotelRoom(String idnum) {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "select roomid, room_name, room_num, detail_room, room_img from ftwi.room where hotelid=\"" + idnum + "\"";

        ArrayList<String[]> rooms = new ArrayList<String[]>();
        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String[] hotel = {rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)};
                    rooms.add(hotel);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }

        return rooms;
    }

    public ArrayList<String[]> getHotelInformation(String idnum) {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "select idhotel, hotel_name from ftwi.hotel_info where managerid=\"" + idnum + "\"";

        ArrayList<String[]> hotels = new ArrayList<String[]>();
        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String[] hotel = {rs.getString(1), rs.getString(2)};
                    hotels.add(hotel);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }

        return hotels;
    }

    public ArrayList<String[]> getHotelWaitingReservation(String idnum) {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "select hotel_name, room_name, idreserve, name, phone, reserve_date "
                + "from ftwi.member, ftwi.hotel_info, ftwi.room, ftwi.reserve "
                + "where managerid=\"" + idnum + "\""
                + " and idnum=userid"
                + " and idhotel=hotelid"
                + " and ftwi.room.roomid=ftwi.reserve.roomid"
                + " and idreserve not in (select reserveid from ftwi.confirm)";

        ArrayList<String[]> reservations = new ArrayList<String[]>();
        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String[] reservation = {rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6)};
                    reservations.add(reservation);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }

        return reservations;
    }

    public ArrayList<String[]> getHotelConfirmedReservation(String idnum) {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "select hotel_name, room_name, idreserve, name, phone, reserve_date "
                + "from ftwi.member, ftwi.hotel_info, ftwi.room, ftwi.reserve "
                + "where managerid=\"" + idnum + "\""
                + " and idnum=userid"
                + " and idhotel=hotelid"
                + " and ftwi.room.roomid=ftwi.reserve.roomid"
                + " and idreserve in (select reserveid from ftwi.confirm)";

        ArrayList<String[]> reservations = new ArrayList<String[]>();
        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String[] reservation = {rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6)};
                    reservations.add(reservation);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }

        return reservations;
    }

    public ArrayList<String[]> getUserReservation(String idnum) {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "(select idreserve, hotel_name, room_name, reserve_date, confirm_date "
                + "from ftwi.hotel_info, ftwi.room, ftwi.reserve, ftwi.confirm "
                + "where userid=\"" + idnum + "\""
                + " and idhotel=hotelid"
                + " and ftwi.room.roomid=ftwi.reserve.roomid"
                + " and reserveid = idreserve) "
                + "union "
                + "(select idreserve, hotel_name, room_name, reserve_date, null as confirm_date "
                + "from ftwi.hotel_info, ftwi.room, ftwi.reserve "
                + "where userid=\"" + idnum + "\""
                + " and idhotel=hotelid"
                + " and ftwi.room.roomid=ftwi.reserve.roomid"
                + " and idreserve not in (select reserveid from ftwi.confirm))";

        ArrayList<String[]> reservations = new ArrayList<String[]>();
        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String[] reservation = {rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)};
                    reservations.add(reservation);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }

        return reservations;
    }

    public ArrayList<String> getRoomReservation(String idnum) {
        Connection conn = getConnection();
        ResultSet rs = null;
        PreparedStatement ps = null;
        String sqlQuery = "select reserve_date "
                + "from ftwi.reserve "
                + "where roomid=\"" + idnum + "\"";

        ArrayList<String> reservations = new ArrayList<String>();
        if (conn != null) {
            try {
                ps = conn.prepareStatement(sqlQuery);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String reservation = rs.getString(1);
                    reservations.add(reservation);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
                closeConnection(conn);
            }
        }

        return reservations;
    }

    public boolean confirmHotelReservation(String reserve_id) {
        Connection conn = getConnection();
        Statement st = null;
        boolean result = true;
        if (conn != null) {
            try {
                st = conn.createStatement();
                Date createTime = new Date(java.lang.System.currentTimeMillis());
                Calendar cal = Calendar.getInstance();
                cal.setTime(createTime);

                String date = String.format("%04d-%02d-%02d", cal.get(Calendar.YEAR),
                        (cal.get(Calendar.MONTH) + 1), cal.get(Calendar.DAY_OF_MONTH));

                String sqlQuery = "INSERT INTO ftwi.confirm "
                        + "(reserveid, confirm_date)"
                        + " VALUES ('" + reserve_id + "', '" + date + "');";
                st.executeUpdate(sqlQuery);
            } catch (SQLException e) {
                e.printStackTrace();
                result = false;
            } finally {
                try {
                    st.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                    result = false;
                }
                closeConnection(conn);
            }
            return result;
        } else {
            System.out.println("Connection Failed!");
            return false;
        }
    }

    public List<String> getList(String category) {
        Connection conn = getConnection();
        List<String> array = new ArrayList<String>();
        if (conn != null) {
            ResultSet rs = null;
            PreparedStatement ps = null;
            String sqlQuery = "select distinct hotel_name from ftwi.hotel_info";
            String sqlQuery2 = "select distinct hotel_addr from ftwi.hotel_info";
            try {
                if (category.equals("name")) {
                    ps = conn.prepareStatement(sqlQuery);
                    rs = ps.executeQuery();
                    while (rs.next()) {
                        array.add(rs.getString("hotel_name"));
                    }
                    rs.close();
                }
                if (category.equals("location")) {
                    ps = conn.prepareStatement(sqlQuery2);
                    rs = ps.executeQuery();
                    while (rs.next()) {
                        array.add(rs.getString("hotel_addr"));
                    }
                    rs.close();
                }
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return array;
    }
}
